#!/usr/bin/python3
#coding=utf-8

import os
import sys
import time
import datetime
from log import CLog
from mysqlDB import CMySqlDB
from ctypes import *
import xlwings as xw
import platform
import command

host = "10.1.54.51"
mysqlPort = 3306
dbUser = "shengli"
dbPwd = "shengli0"
hisTradingDB = "fut_one_history_trading_data"

##################################################################################################################
class dataStruct:
    orderCount = 0
    orderQty = 0
    execQty = 0


cexe = "call.exe"
if platform.system().lower() == "linux":
    cexe = "./call"
dateOrderData = {"emptyData": dataStruct}
today = datetime.datetime.now().strftime('%Y%m%d')
execLog = CLog(logName="rem_trade_info_statisticl_" + today + ".log", isPrint=True)
EngAlphabets = ["A", "B", "C", "D", "E", "F", "G",
                "H", "I", "J", "K", "L", "M", "N",
                "O", "P", "Q", "R", "S", "T",
                "U", "V", "W", "X", "Y", "Z"]
# open Excel exe
app = xw.App(visible=False,add_book=False)
wb = app.books.add()
# save Execl file
xlsxName = r"./rem_trade_info_statisticl_" + datetime.datetime.now().strftime('%Y%m%d%H%M%S') + ".xlsx"
wb.save(xlsxName)
db = CMySqlDB(log=execLog, dbHost=host, dbPort=mysqlPort, dbName=hisTradingDB, dbUser=dbUser, dbPwd=dbPwd)
db.query(sqlStr="show tables")
data = db.getData()
execLog.log("get tables count:" + str(len(data)))

#for every table
for d in data:
    # trading db
    if "t_bk_data" in d[0] or "t_fut_executions" in d[0]:
        continue

    if "t_fut_orders" == d[0]:
        pass
    # his trading db
    elif "t_fut_orders_" in d[0]:
        today = d[0][13:]
    db.query("select count(1) from " + d[0])
    nowData = dataStruct
    nowData.orderCount = db.getData()[0][0]
    db.query("select sum(quantity) from " + d[0])
    nowData.orderQty = db.getData()[0][0]
    db.query("select sum(executed_qty) from " + d[0])
    nowData.execQty = db.getData()[0][0]
    dateOrderData[today] = nowData
    db.query("select order_shengli_accept_time from " + d[0])
    timeStamps = db.getData()
    #yyyy-mm-dd hh:mm:ss 0
    times = {"yyyy-mm-dd hh:mm:ss":0}
    maxTimes = {"yyyy-mm-dd hh:mm:ss":0}
    #for every order s timestamp
    for timeStamp in timeStamps:
            execCmdLine = cexe + " " + str(timeStamp)[1:-2]
            realTime = os.popen(execCmdLine).read()
            if realTime in times.keys():
                times[realTime] = times[realTime] + 1
            else:
                times[realTime] = 1
    execLog.log("deal one day order finish date is " + str(today))

    maxTimeList = [""]
    #get calc 100 max result
    for index in range(0, 10):
        maxTime = ""
        maxTimeCount = 0
        for item in times.items():
            if item[1] > maxTimeCount:
                maxTime = item[0]
                maxTimeCount = item[1]
        maxTimes[maxTime] = maxTimeCount
        times[maxTime] = 0
        maxTimeList.append(maxTime)
        #execLog.log("get top value " + str(index + 1))

    #write data to execl
    wb.sheets.add(today)
    wb.sheets[today].range("A1").value = "order count"
    wb.sheets[today].range("A2").value = "order quantity"
    wb.sheets[today].range("A3").value = "executed qty"
    wb.sheets[today].range("B1").value = nowData.orderCount
    wb.sheets[today].range("B2").value = nowData.orderQty
    wb.sheets[today].range("B3").value = nowData.execQty

    # the first data is ""
    for i in range(1, 11):
        wb.sheets[today].range(EngAlphabets[4]+str(i)).value = "top "+str(i)
        wb.sheets[today].range(EngAlphabets[5]+str(i)).value = maxTimeList[i]
        wb.sheets[today].range(EngAlphabets[6]+str(i)).value = maxTimes[maxTimeList[i]]

wb.save()
wb.close()
app.quit()